package com.demo2do.fms.admin.entity.report.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 * 回单确认导出
 *
 * @author Ark
 *
 */
public class TransOrderReportSearch implements QueryBuilder {

    /** 所选择公司 */
    private Long organizationId;
    
    /** 派车单号 */
    private String dispatchOrderCode;
    
    /** 驳运车号 */
    private Long vehicleId;
    
    /** 安吉调度指令组号 */
    private String shipmentGroupId;
    
    /** 起始地 装卸地 */
    private Long startFacilityId;
    
    /** 目的地 装卸地 */
    private Long destinationFacilityId;
    
    /** 开始派车单日期 */
    private String beginDispatchDate;
    
    /** 结束派车单日期 */
    private String endDispatchDate;
    
    /** 主驾驶员 */
    private Long mainDriverId;
    
    /** 第一副驾驶员 */
    private Long firstViceDriverId;
    
    /** 是否隐藏全部回单的派车单  */
    private Boolean accomplishConfirm;
    
    /** 导出条数上限 */
    private Integer limit;
    
    private QueryFilter filter;
    
    private QueryOrder order;
    
    /**
     * The default constructor
     */
    public TransOrderReportSearch() {
        order = new QueryOrder();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (filter == null || refresh) {
            
            filter = new QueryFilter().addEquals("a", "organization_id", organizationId)
                                      .addEquals("a", "dispatch_order_code", dispatchOrderCode)
                                      .addEquals("a", "vehicle_id", vehicleId)
                                      .addEquals("a", "shipment_group_id", shipmentGroupId)
                                      .addEquals("b", "id", startFacilityId)
                                      .addEquals("c", "id", destinationFacilityId)
                                      .addEquals("e", "id", mainDriverId)
                                      .addEquals("f", "id", firstViceDriverId)
                                      .addEquals("a", "is_accomplish_confirm", accomplishConfirm)
                                      .addLessThanEqual("ROWNUM", limit);
            
            if (StringUtils.isNotBlank(beginDispatchDate)) {
                filter.addSentence("a.dispatch_date >= TO_DATE('" + beginDispatchDate.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endDispatchDate)) {
                filter.addSentence("a.dispatch_date <= TO_DATE('" + endDispatchDate.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
        }
        
        return filter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT o.organization_name AS organizationName, ")
                     .append("a.dispatch_order_code AS code, ")
                     .append("a.dispatch_date AS dispatchDate, ")
                     .append("a.shipment_group_id AS shipmentGroupId, ")
                     .append("b.facility_code || ' - ' || b.facility_name AS startFacility, ")
                     .append("c.facility_code || ' - ' || c.facility_name AS destinationFacility, ")
                     .append("d.vehicle_code || ' - ' || d.vehicle_name AS vehicle, ")
                     .append("e.name AS mainDriver, ")
                     .append("f.name AS firstViceDriver, ")
                     .append("a.actual_load_number AS actualLoadNumber, ")
                     .append("a.accomplish_confirm_number AS accomplishConfirmNumber, ")
                     .append("a.is_expense_reimbursed AS expenseReimbursed ")
              .append("FROM tbl_truck_dispatch_order a, vw_load_place b, vw_load_place c, vw_vehicle d, tbl_all_employee_info e, tbl_all_employee_info f, vw_transportation_company o ")
              .append("WHERE a.start_facility_id = b.id(+) AND a.destination_facility_id = c.id(+) AND a.vehicle_id = d.id(+) AND a.main_driver_id = e.id(+) AND a.first_vice_driver_id = f.id(+) AND a.is_disabled = 0 AND a.organization_id = o.id AND a.actual_load_number > 0 ")
              .append(getPreparedFilter(false).clauseSentence() + order.sentence());
        
        return buffer.toString();
        
    }
    
    /**
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("organizationName", rs.getObject("organizationName"));
                result.put("code", rs.getObject("code"));
                result.put("dispatchDate", rs.getObject("dispatchDate"));
                result.put("shipmentGroupId", rs.getObject("shipmentGroupId"));
                result.put("startFacility", rs.getObject("startFacility"));
                result.put("destinationFacility", rs.getObject("destinationFacility"));
                result.put("vehicle", rs.getObject("vehicle"));
                result.put("mainDriver", rs.getObject("mainDriver"));
                result.put("firstViceDriver", rs.getObject("firstViceDriver"));
                result.put("actualLoadNumber", rs.getObject("actualLoadNumber"));
                result.put("accomplishConfirmNumber", rs.getObject("accomplishConfirmNumber"));
                result.put("expenseReimbursed", rs.getBoolean("expenseReimbursed"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return order;
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the dispatchOrderCode
     */
    public String getDispatchOrderCode() {
        return dispatchOrderCode;
    }

    /**
     * @return the vehicleId
     */
    public Long getVehicleId() {
        return vehicleId;
    }

    /**
     * @return the shipmentGroupId
     */
    public String getShipmentGroupId() {
        return shipmentGroupId;
    }

    /**
     * @return the startFacilityId
     */
    public Long getStartFacilityId() {
        return startFacilityId;
    }

    /**
     * @return the destinationFacilityId
     */
    public Long getDestinationFacilityId() {
        return destinationFacilityId;
    }

    /**
     * @return the beginDispatchDate
     */
    public String getBeginDispatchDate() {
        return beginDispatchDate;
    }

    /**
     * @return the endDispatchDate
     */
    public String getEndDispatchDate() {
        return endDispatchDate;
    }

    /**
     * @return the mainDriverId
     */
    public Long getMainDriverId() {
        return mainDriverId;
    }

    /**
     * @return the firstViceDriverId
     */
    public Long getFirstViceDriverId() {
        return firstViceDriverId;
    }

    /**
     * @return the accomplishConfirm
     */
    public Boolean getAccomplishConfirm() {
        return accomplishConfirm;
    }
    
    /**
     * @return
     */
    public Integer getLimit() {
        return limit;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param dispatchOrderCode the dispatchOrderCode to set
     */
    public void setDispatchOrderCode(String dispatchOrderCode) {
        this.dispatchOrderCode = dispatchOrderCode;
    }

    /**
     * @param vehicleId the vehicleId to set
     */
    public void setVehicleId(Long vehicleId) {
        this.vehicleId = vehicleId;
    }

    /**
     * @param shipmentGroupId the shipmentGroupId to set
     */
    public void setShipmentGroupId(String shipmentGroupId) {
        this.shipmentGroupId = shipmentGroupId;
    }

    /**
     * @param startFacilityId the startFacilityId to set
     */
    public void setStartFacilityId(Long startFacilityId) {
        this.startFacilityId = startFacilityId;
    }

    /**
     * @param destinationFacilityId the destinationFacilityId to set
     */
    public void setDestinationFacilityId(Long destinationFacilityId) {
        this.destinationFacilityId = destinationFacilityId;
    }

    /**
     * @param beginDispatchDate the beginDispatchDate to set
     */
    public void setBeginDispatchDate(String beginDispatchDate) {
        this.beginDispatchDate = beginDispatchDate;
    }

    /**
     * @param endDispatchDate the endDispatchDate to set
     */
    public void setEndDispatchDate(String endDispatchDate) {
        this.endDispatchDate = endDispatchDate;
    }

    /**
     * @param mainDriverId the mainDriverId to set
     */
    public void setMainDriverId(Long mainDriverId) {
        this.mainDriverId = mainDriverId;
    }

    /**
     * @param firstViceDriverId the firstViceDriverId to set
     */
    public void setFirstViceDriverId(Long firstViceDriverId) {
        this.firstViceDriverId = firstViceDriverId;
    }

    /**
     * @param accomplishConfirm the accomplishConfirm to set
     */
    public void setAccomplishConfirm(Boolean accomplishConfirm) {
        this.accomplishConfirm = accomplishConfirm;
    }
    
    /**
     * @param limit
     */
    public void setLimit(Integer limit) {
        this.limit = limit;
    }
    
}
